<?php
declare (strict_types = 1);

namespace app\controller;

use think\Request;
use app\BaseController;
use think\facade\View;
use app\middleware\CheckLogin;
use app\model\SalesStock as StockModel;
use app\model\SettingCar as CarModel;
use app\model\SalesOrder as OrderModel;
use app\model\ServiceCustomer as CustomerModel;
use app\model\UploadFile as FileModel;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Style\Alignment as PHPExcel_Style_Alignment;
use PhpOffice\PhpSpreadsheet\Style\fill as PHPExcel_Style_Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;

class Stock extends BaseController
{
    // 注册控制器中间件
    protected $middleware = [CheckLogin::class];

    //主页
    public function index()
    {
        return redirect((string)url('Stock/undelivered'));
    }

    /**
     * 显示在库库存列表
     *
     * @return \think\Response
     */
    public function undelivered()
    {   
        //获取此前数据
        $data = StockModel::where('deliver',0)->select();
        for ($i=0; $i < count($data); $i++) { 
            $data[$i]['age'] = intval(abs((strtotime($data[$i]['create_date']) - strtotime('now')) / 86400));
            if(!empty($data[$i]['certificate'])){
                $url = FileModel::find($data[$i]['certificate']);
                $data[$i]['certificate_url'] = $url;
            }
        }
        View::assign('data',$data);

        return view('undelivered'); 
    }

    /**
     * 显示已经交付库存列表
     *
     * @return \think\Response
     */
    public function past()
    {   
        return view('past'); 
    }

        /**
     * 保有客户数据json列表
     *
     * @param  \think\Request  $request
     * @return \think\Response
     */
    public function pastJson(Request $request)
    {
        //获取传来的条件数据
        $request = $request->param();

        // //返回让表格绘制表格的指令
        $data['draw'] = intval($request['draw']);

        //总记录数
        $data['recordsTotal'] = StockModel::where('deliver',1)->count();

            //记录起
        $start = $request['start'];

        //查询长度
        $length = $request['length'];

        //排序条件
        $order = $request['order']['0']['column'];

        // //排序条件
        $orderArr = [ 0 =>'create_date', 1 =>'deliver_date', 3 =>'vehicle'];

        //获取要排序的字段
        $orderField = (empty($orderArr[$order]))?'deliver_date':$orderArr[$order];

        //排序方式
        $asc = $request['order']['0']['dir'];

        //获取搜索后记录数
        if(!empty($request['search']['value'])){
            $research = StockModel::where('model|vin|engine|location|storehouse|regulator','like','%'.$request['search']['value'].'%')->where('deliver',1)->select();
            $data['recordsFiltered'] = count($research);
        }else{
            $data['recordsFiltered'] = $data['recordsTotal'];
        }

        //获取已经交付车辆数据
        $stockData = StockModel::field('id,create_date,vehicle,config,trim,color,vin,phone,deliver_date')->where('model|vin|engine|location|storehouse|regulator','like','%'.$request['search']['value'].'%')->where('deliver',1)->order($orderField,$asc)->limit(intval($start), intval($length))->select();

        for ($i=0; $i < count($stockData); $i++) { 
            if(!empty($stockData[$i]['create_date'])){
                $stockData[$i]['create_date'] = date('Y/m/d',strtotime($stockData[$i]['create_date']));
            }else{
                $stockData[$i]['create_date'] = '无数据';
            }
            if(!empty($stockData[$i]['deliver_date'])){
                $stockData[$i]['deliver_date'] = date('Y/m/d',strtotime($stockData[$i]['deliver_date']));
                $stockData[$i]['age'] = intval(abs((strtotime($stockData[$i]['create_date']) - strtotime($stockData[$i]['deliver_date'])) / 86400));
            }else{
                $stockData[$i]['deliver_date'] = '无数据';
                 $stockData[$i]['age'] = '无';
            }
            if(!empty($stockData[$i]['phone'])){
                $name = CustomerModel::where('owner_phone',$stockData[$i]['phone'])->value('owner_name');
                if(!empty($name)){
                    $stockData[$i]['name'] = mb_substr($name, 0,6);
                }else{
                    $stockData[$i]['name'] = '无数据';
                }
            }else{
                $stockData[$i]['phone'] = '无数据';
                $stockData[$i]['name'] = '无数据';
            }
        }

        $data['data'] = $stockData;

        return json($data);
        // dump($data);

    }

    /**
     * 显示创建资源表单页.
     *
     * @return \think\Response
     */
    public function create()
    {
        //获取车系数据
        $vehicleOption = CarModel::where('valid','1')->Distinct(true)->column('vehicle');
        View::assign('vehicleOption',$vehicleOption);

        //获取存放地列表
        $location = StockModel::Distinct(true)->column('location');
        View::assign('location',$location);

        //获取库位列表
        $storehouse = StockModel::Distinct(true)->column('storehouse');
        View::assign('storehouse',$storehouse);

        //获取监管方列表
        $regulator = StockModel::Distinct(true)->column('regulator');
         View::assign('regulator',$regulator);

        return view('create'); 
    }

        /**
     * 上传文件.
     *
     * @param  \think\Request  $request
     * @return \think\Response
     */
    public function fileUpload(Request $request)
    {
        // 获取表单上传文件 例如上传了001.jpg
        $file = $request->file('input');
        $stock_id = $request->param('id');

        //判断是否上传文件
        if(!empty($file)){

            //创建文件名数组
            $url = \think\facade\Filesystem::disk('public')->putFile( 'stock', $file);
            $upload_file['url'] = $url;
            $upload_file['class'] = "stock";
            $upload_file['ext'] = pathinfo($url, PATHINFO_EXTENSION);
            $upload_file_ID = FileModel::create($upload_file);

            //获取文件路径
            $data['certificate_url'] = $url;
            $data['certificate'] = $upload_file_ID;
            $data['result'] = true;
        }else{
            $data['result'] = false;
            $data['reason'] = '没有文件上传';
        }
        return json($data);
    }

    /**
     * 保存新建的资源
     *
     * @param  \think\Request  $request
     * @return \think\Response
     */
    public function save(Request $request)
    {
        $data = request()->param();
        $result = StockModel::create($data);//调用create方法写入数据库
        if($result == false){
            return 0;
        }else{
            return $result->id;
        }
    }

    /**
     * 显示指定的资源
     *
     * @param  int  $id
     * @return \think\Response
     */
    public function read($id)
    {
        //获取此前数据
        $data = StockModel::find($id);

        if($data['deliver'] == 0){
             $data['age'] = intval(abs((strtotime($data['create_date']) - strtotime('now')) / 86400));
        }else{
            if(empty($data['deliver_date'])){
                $data['age'] = '交车时间未填写，无法计算';
            }else{
                $data['age'] = intval(abs((strtotime($data['create_date']) - strtotime($data['deliver_date'])) / 86400));
            }
        }

        if(!empty($data['certificate'])){
            $file = FileModel::find($data['certificate']);
            $data['certificate_url'] = $file['url'];
        }

        if(!empty($data['phone'])){
                //查询客户信息
                $customer = CustomerModel::where('owner_phone',$data['phone'])->find();
                $data['customer_id'] = $customer['id'];
                $data['name'] =  mb_substr($customer['owner_name'], 0,6) ;
            }

        View::assign('data',$data);

        return view('read');
    }

    /**
     * 显示编辑资源表单页.
     *
     * @param  int  $id
     * @return \think\Response
     */
    public function edit($id)
    {
        //获取此前数据
        $data = StockModel::find($id);
        View::assign('data',$data);

        //获取车系数据
        $vehicleOption = CarModel::where('valid','1')->Distinct(true)->column('vehicle');
        View::assign('vehicleOption',$vehicleOption);

        //获取配置数据
        $configOption = CarModel::where('valid','1')->where('vehicle',$data['vehicle'])->Distinct(true)->column('config');
        View::assign('configOption',$configOption);

        //确定当前车型
        $carWhere['vehicle'] = $data['vehicle'];
        $carWhere['config'] = $data['config'];

        //获取当前车型配置数据
        $carData = CarModel::where($carWhere)->find();
        if(!empty($carData)){
            //获取当前车型颜色数据
            $colorOption = explode(',',$carData['color']);
            View::assign('colorOption',$colorOption);

            //获取当前车型内饰数据
            $trimOption = explode(',',$carData['trim']);
            View::assign('trimOption',$trimOption);
        }else{
            $colorOption = '配置输入有误';
            View::assign('colorOption',$colorOption);
            $trimOption = '配置输入有误';
            View::assign('trimOption',$trimOption);
        }

        //获取合格证文件地址
        if(!empty($data['certificate'])){
            $certificate = FileModel::find($data['certificate']);
            View::assign('certificate_url',$certificate['url']);
        }

        //获取存放地列表
        $location = StockModel::Distinct(true)->column('location');
        View::assign('location',$location);

        //获取库位列表
        $storehouse = StockModel::Distinct(true)->column('storehouse');
        View::assign('storehouse',$storehouse);

        //获取监管方列表
        $regulator = StockModel::Distinct(true)->column('regulator');
         View::assign('regulator',$regulator);

        return view('edit'); 
    }

    /**
     * 保存更新的资源
     *
     * @param  \think\Request  $request
     * @param  int  $id
     * @return \think\Response
     */
    public function update(Request $request, $id)
    {
        //获取数据
        $data = request()->param();
        //更新数据
        $result = StockModel::update($data);//调用create方法写入数据库
        if($result == false){
            return 0;
        }else{
            return $result->id;
        }
    }

    /**
     * 删除指定资源
     *
     * @param  int  $id
     * @return \think\Response
     */
    public function delete($id)
    {
        //删除
        $car = StockModel::find($id);//删除id号
        $result = $car->delete();//确认删除结果，返回布尔值
        if($result){
            return "ok";//成功则返回OK
        }else{
            return "false";//失败则返回false
        }
    }

    /**
     * 获取的资源
     *
     * @param  \think\Request  $request
     * @return \think\Response
     */
    public function query(Request $request)
    {
        //获取传递来的信息
        $where['vehicle'] = $request->param('vehicle');
        $where['config'] = $request->param('config');
        $where['color'] = $request->param('color');
        $where['trim'] = $request->param('trim');

        $data = StockModel::where($where)->select();

        return json($data);
    }

    /**
     * 批量模板导入数据的资源
     *
     * @param  \think\Request  $request
     * @return \think\Response
     */
    
    public function input(Request $request)
    {
        // 获取表单上传文件 例如上传了001.jpg
        $file = request()->file('input-excel');

        //如果上传文件不为空
        if(!empty($file)){
            // 上传到本地服务器
            $savename = \think\facade\Filesystem::disk('temp')->putFile('StockExcel',$file);
            // //获取文件路径
            $path = public_path().'runtime/temp/'.$savename;

            $objPHPExcel = PHPExcel_IOFactory::load($path,$encode = 'utf-8');
     
            $sheet = $objPHPExcel->getSheet(0)->toArray();

            //检查模板有没有问题
            if($sheet[0][9] != "车架号"){
                $return['result'] = false;
                $return['reason'] = '模板错误';
                $return['count'] = 0;
                $return['blankRow'] = 0;
            }else{

                $isEmpty = false;
                // 检查数据
                for ($i=0; $i < count($sheet); $i++) { 
                    if(empty($sheet[$i][0])){
                        $return['result'] = false;
                        $return['reason'] = '入库时间不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][9]; 
                    }
                    if(empty($sheet[$i][1])){
                        $return['result'] = false;
                        $return['reason'] = '车系不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][9];
                    }
                    if(empty($sheet[$i][2])){
                        $return['result'] = false;
                        $return['reason'] = '配置不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][9];
                    }
                    if(empty($sheet[$i][3])){
                        $return['result'] = false;
                        $return['reason'] = '颜色不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][9];
                    }
                    if(empty($sheet[$i][4])){
                        $return['result'] = false;
                        $return['reason'] = '内饰不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][9];
                    }
                    if(empty($sheet[$i][5])){
                        $return['result'] = false;
                        $return['reason'] = '指导价格不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][9];
                    }
                    if(empty($sheet[$i][6])){
                        $return['result'] = false;
                        $return['reason'] = '车型编号不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][9];
                    }
                    if(empty($sheet[$i][9])){
                        $return['result'] = false;
                        $return['reason'] = '车架号不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][9];
                    }
                    if(empty($sheet[$i][10])){
                        $return['result'] = false;
                        $return['reason'] = '发动机号不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][9];
                    }
                    if(empty($sheet[$i][11])){
                        $return['result'] = false;
                        $return['reason'] = '存放地点不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][9];
                    }
                    if(empty($sheet[$i][13])){
                        $return['result'] = false;
                        $return['reason'] = '监管方不能为空';
                        $return['count'] = 0;
                        $return['blankRow'] = 0;
                        $isEmpty = true;
                        $return['vin'] = $sheet[$i][9];
                    }
                }

                if($isEmpty == false){
                    $data = array();
                    for ($i=1; $i < count($sheet); $i++) {
                        $vin = $sheet[$i][9];
                        //判断这一行是否有车架号信息
                        $blankRow = 0;
                        if(!empty($vin)){
                            //以车架号判断是否有重复的车辆信息
                            $old = StockModel::where('vin',$vin)->find();
                            if(empty($old)){
                                $data[$i]['create_date'] = date("Y-m-d",strtotime($sheet[$i][0]));
                                $data[$i]['vehicle'] = trim($sheet[$i][1]);
                                $data[$i]['config'] = $sheet[$i][2];
                                $data[$i]['color'] = trim($sheet[$i][3]);
                                $data[$i]['trim'] = trim($sheet[$i][4]);
                                $data[$i]['price'] = trim($sheet[$i][5]);
                                $data[$i]['model'] = trim($sheet[$i][6]);
                                $data[$i]['option'] = trim($sheet[$i][7]);
                                $data[$i]['note'] = trim($sheet[$i][8]);
                                $data[$i]['vin'] = trim($sheet[$i][9]);
                                $data[$i]['engine'] = trim($sheet[$i][10]);
                                $data[$i]['location'] = trim($sheet[$i][11]);
                                $data[$i]['storehouse'] = trim($sheet[$i][12]);
                                $data[$i]['regulator'] = trim($sheet[$i][13]);
                                $data[$i]['deliver'] = trim($sheet[$i][14]);
                            }
                        }else{
                            $blankRow = $blankRow + 1;
                        }  
                    }
                    $user = new StockModel;
                    $saveResult = $user->saveAll($data);
                    $return['count'] = count($saveResult);
                    $return['result'] = true;
                    $return['reason'] = '没有错误';
                    $return['blankRow'] = $blankRow;
                }
            }
        }else{
            $return['result'] = false;
            $return['reason'] = '未上传文件';
            $return['count'] = 0;
            $return['blankRow'] = 0;
        }
        
        return json($return);
    }

    /**
     * 批量导出数据的资源
     *
     * @param  \think\Request  $request
     * @return \think\Response
     */

    public function output()
    {
        //获取还未交车的库存数据
        $Stock = StockModel::where('deliver',0)->order('create_date','asc')->select();

        //获取已经交车的库存数据
        $Deliver = StockModel::where('deliver',1)->order('create_date','asc')->select();

        //创建PHPExcel对象
        $objPHPExcel = new Spreadsheet();

        //设置文件描述信息
        $objPHPExcel->getProperties()->setCreator('桂林灵川WEY'); //设置创建者
        $objPHPExcel->getProperties()->setLastModifiedBy(''); //设置修改者
        $objPHPExcel->getProperties()->setTitle('桂林灵川WEY库存表'); //设置标题
        $objPHPExcel->getProperties()->setSubject('桂林灵川WEY库存表'); //设置主题
        $objPHPExcel->getProperties()->setDescription(''); //设置描述
        $objPHPExcel->getProperties()->setKeywords('桂林灵川WEY库存表');//设置关键词
        $objPHPExcel->getProperties()->setCategory('');//设置类型


        //设置数据

        
        // 设置当前sheet为第1页
        $objPHPExcel->setActiveSheetIndex(0);
         // 设置当前sheet的名称
        $objPHPExcel->getActiveSheet()->setTitle('库存表');
        //设置数据来源
        $data = $Stock;

        // 列标
        $list = ['A', 'B', 'C','D','E','F','G','H','I','J','K','L','M','N','O','P'];

        // 设置第一行加粗和左对齐
        foreach ($list as $col) {
            
            //设置水平居中
            if($col != 'I'){
                $objPHPExcel->getActiveSheet()
                    ->getStyle($col)->getAlignment()
                    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            }

            //设置垂真居中
            $objPHPExcel->getActiveSheet()
                ->getStyle($col)->getAlignment()
                ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            
            
            // 设置第一行加粗
            $objPHPExcel->getActiveSheet()->getStyle($col.'1')
                ->getFont()->setBold(true);

            //设置第一行背景颜色
            $objPHPExcel->getActiveSheet()->getStyle($col.'1')
                ->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
                ->getStartColor()->setRGB('2f6921');//设置第一行背景色为ccffff
        }

        //逐行设置行高
        for ($i=1; $i < count($data)+2; $i++) { 
            //设置行高
            $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(30);
        }

        // 设置列宽
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);//到库时间
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(7);//库龄
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(8);//车系
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(22);//配置
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);//颜色
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);//内饰
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);//指导价
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(17);//车型编码
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(16);//选装
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(14);//备注
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(21);//车架号
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(23);//发动机号
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(12);//存放地点
        $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(7);//库位
        $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(12);//监管方
        $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(8);//是否配车

        // 填充第一行数据
        $objPHPExcel->getActiveSheet()
            ->setCellValue($list[0] . '1', '入库时间')
            ->setCellValue($list[1] . '1', '库龄')
            ->setCellValue($list[2] . '1', '车系')
            ->setCellValue($list[3] . '1', '配置')
            ->setCellValue($list[4] . '1', '颜色')
            ->setCellValue($list[5] . '1', '内饰')
            ->setCellValue($list[6] . '1', '指导价')
            ->setCellValue($list[7] . '1', '车辆型号')
            ->setCellValue($list[8] . '1', '选装')
            ->setCellValue($list[9] . '1', '备注')
            ->setCellValue($list[10] . '1', '车架号')
            ->setCellValue($list[11] . '1', '发动机号')
            ->setCellValue($list[12] . '1', '位置')
            ->setCellValue($list[13] . '1', '库存')
            ->setCellValue($list[14] . '1', '监管方')
            ->setCellValue($list[15] . '1', '是否配车');

        // 设置数据
        // 定义一个i变量，目的是在循环输出数据是控制行数
        for ($i=0; $i < count($data); $i++) { 
            $age = intval(abs((strtotime($data[$i]['create_date']) - strtotime('now')) / 86400));
            $mated = $data[$i]['mated'] == 1?'是':'否';

            //当前行数
            $row = $i + 2;
            $objPHPExcel->getActiveSheet()
                ->setCellValue($list[0] . $row, date('Y/m/d',strtotime($data[$i]['create_date'])))
                ->setCellValue($list[1] . $row, $age)
                ->setCellValue($list[2] . $row, $data[$i]['vehicle'])
                ->setCellValue($list[3] . $row, $data[$i]['config'])
                ->setCellValue($list[4] . $row, $data[$i]['color'])
                ->setCellValue($list[5] . $row, $data[$i]['trim'])
                ->setCellValue($list[6] . $row, $data[$i]['price'])
                ->setCellValue($list[7] . $row, $data[$i]['model'])
                ->setCellValue($list[8] . $row, $data[$i]['option'])
                ->setCellValue($list[9] . $row, $data[$i]['note'])
                ->setCellValue($list[10] . $row, $data[$i]['vin'])
                ->setCellValue($list[11] . $row, $data[$i]['engine'])
                ->setCellValue($list[12] . $row, $data[$i]['location'])
                ->setCellValue($list[13] . $row, $data[$i]['storehouse'])
                ->setCellValue($list[14] . $row, $data[$i]['regulator'])
                ->setCellValue($list[15] . $row, $mated);
        }

        //第二sheet转正
        $objPHPExcel->createSheet();
        $objPHPExcel->setActiveSheetIndex(1);
        // 设置当前sheet的名称
        $objPHPExcel->getActiveSheet()->setTitle('已交车辆表');
        //设置数据来源
        $data = $Deliver;

        // 列标
        $list = ['A', 'B', 'C','D','E','F','G','H','I','J','K','L','M','N','O','P'];

        // 设置第一行加粗和左对齐
        foreach ($list as $col) {
            
            //设置水平居中
            if($col != 'I'){
                $objPHPExcel->getActiveSheet()
                    ->getStyle($col)->getAlignment()
                    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            }

            //设置垂真居中
            $objPHPExcel->getActiveSheet()
                ->getStyle($col)->getAlignment()
                ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            
            
            // 设置第一行加粗
            $objPHPExcel->getActiveSheet()->getStyle($col.'1')
                ->getFont()->setBold(true);

            //设置第一行背景颜色
            $objPHPExcel->getActiveSheet()->getStyle($col.'1')
                ->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
                ->getStartColor()->setRGB('2f6921');//设置第一行背景色为ccffff
        }

        //逐行设置行高
        for ($i=1; $i < count($data)+2; $i++) { 
            //设置行高
            $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(30);
        }

        // 设置列宽
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);//到库时间
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(7);//库龄
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(8);//车系
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(22);//配置
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);//颜色
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);//内饰
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);//指导价
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(17);//车型编码
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(16);//选装
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(14);//备注
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(21);//车架号
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(23);//发动机号
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(12);//存放地点
        $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(7);//库位
        $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(12);//监管方
        $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(8);//是否配车

        // 填充第一行数据
        $objPHPExcel->getActiveSheet()
            ->setCellValue($list[0] . '1', '入库时间')
            ->setCellValue($list[1] . '1', '库龄')
            ->setCellValue($list[2] . '1', '车系')
            ->setCellValue($list[3] . '1', '配置')
            ->setCellValue($list[4] . '1', '颜色')
            ->setCellValue($list[5] . '1', '内饰')
            ->setCellValue($list[6] . '1', '指导价')
            ->setCellValue($list[7] . '1', '车辆型号')
            ->setCellValue($list[8] . '1', '选装')
            ->setCellValue($list[9] . '1', '备注')
            ->setCellValue($list[10] . '1', '车架号')
            ->setCellValue($list[11] . '1', '发动机号')
            ->setCellValue($list[12] . '1', '位置')
            ->setCellValue($list[13] . '1', '库存')
            ->setCellValue($list[14] . '1', '监管方')
            ->setCellValue($list[15] . '1', '是否配车');

        // 设置数据
        // 定义一个i变量，目的是在循环输出数据是控制行数
        for ($i=0; $i < count($data); $i++) { 
            $age = intval(abs((strtotime($data[$i]['create_date']) - strtotime('now')) / 86400));
            $mated = $data[$i]['mated'] == 1?'是':'否';

            //当前行数
            $row = $i + 2;
            $objPHPExcel->getActiveSheet()
                ->setCellValue($list[0] . $row, date('Y/m/d',strtotime($data[$i]['create_date'])))
                ->setCellValue($list[1] . $row, $age)
                ->setCellValue($list[2] . $row, $data[$i]['vehicle'])
                ->setCellValue($list[3] . $row, $data[$i]['config'])
                ->setCellValue($list[4] . $row, $data[$i]['color'])
                ->setCellValue($list[5] . $row, $data[$i]['trim'])
                ->setCellValue($list[6] . $row, $data[$i]['price'])
                ->setCellValue($list[7] . $row, $data[$i]['model'])
                ->setCellValue($list[8] . $row, $data[$i]['option'])
                ->setCellValue($list[9] . $row, $data[$i]['note'])
                ->setCellValue($list[10] . $row, $data[$i]['vin'])
                ->setCellValue($list[11] . $row, $data[$i]['engine'])
                ->setCellValue($list[12] . $row, $data[$i]['location'])
                ->setCellValue($list[13] . $row, $data[$i]['storehouse'])
                ->setCellValue($list[14] . $row, $data[$i]['regulator'])
                ->setCellValue($list[15] . $row, $mated);
        }

        // 设置当前sheet为第1页
        $objPHPExcel->setActiveSheetIndex(0);

        //数据导出
        ob_end_clean();
        
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename=桂林长道WEY库存表.xlsx');
        header('Cache-Control: max-age=0'); //禁止缓存
        $objWriter = new Xlsx($objPHPExcel);
        $file = $objWriter->save('php://output');
        $objPHPExcel->disconnectWorksheets();
        exit; //必须 终止
    }

    /**
     * 库存查询页面
     *
     * @return \think\Response
     */
    public function queryStock()
    {
        //获取车系数据
        $vehicleOption = StockModel::Distinct(true)->where('deliver',0)->column('vehicle');
        View::assign('vehicleOption',$vehicleOption);

        return view('queryStock');
    }

    /**
     * 库存车辆配置查询页面
     *
     * @return \think\Response
     */

    public function stockQueyConfigJson(Request $request)
    {
        //获取车系数据
        $vehicle = $request->param('vehicle');

        //获取车型配置数据
        $config = StockModel::where('vehicle',$vehicle)->Distinct(true)->where('deliver',0)->column('config');

        //返回
        return json($config);
    }

    /**
     * 库存车辆颜色查询页面
     *
     * @return \think\Response
     */

    public function stockQueyColorJson(Request $request)
    {
        //获取车系数据
        $vehicle['vehicle'] = $request->param('vehicle');

        //获取配置数据
        $vehicle['config'] = $request->param('config');

        //获取车型配置数据
        $color = StockModel::where($vehicle)->Distinct(true)->where('deliver',0)->column('color');

        //返回
        return json($color);
    }

    /**
     * 库存车辆内饰查询页面
     *
     * @return \think\Response
     */

    public function stockQueyTrimJson(Request $request)
    {
        //获取车系数据
        $vehicle['vehicle'] = $request->param('vehicle');

        //获取配置数据
        $vehicle['config'] = $request->param('config');

        //获取配置数据
        $vehicle['color'] = $request->param('color');

        //获取车型配置数据
        $trim = StockModel::where($vehicle)->Distinct(true)->column('trim');

        //返回
        return json($trim);
    }

    /**
     * 库存查询Json
     *
     * @param  \think\Request  $request
     * @return \think\Response
     */
    public function queryJson(Request $request)
    {
        $where = array();
        //获取传来的条件数据
        if(!empty($request->param('vehicle'))){
            $where['vehicle'] = $request->param('vehicle');
        }
        if(!empty($request->param('config'))){
            $where['config'] = $request->param('config');
        }
        if(!empty($request->param('color'))){
            $where['color'] = $request->param('color');
        }
        if(!empty($request->param('trim'))){
            $where['trim'] = $request->param('trim');
        }

        //获取已经交付车辆数据
        $stockData = StockModel::field('create_date,vehicle,config,color,trim,option,note,vin,location')->where($where)->where('deliver',0)->where('mated',0)->order('create_date','asc')->select();
        //计算库龄
        for ($i=0; $i <count($stockData) ; $i++) { 
            $stockData[$i]['age'] = intval(abs((strtotime(date('Y-m-d')) - strtotime($stockData[$i]['create_date'])) / 86400));
            $stockData[$i]['create_date'] = date('m/d',strtotime($stockData[$i]['create_date']));
        }

        $result['data'] = $stockData;

        return json($result);
    }

    /**
     * 库存车辆调出
     *
     * @param  \think\Request  $request
     * @param  int  $id
     * @return \think\Response
     */
    public function callout(Request $request, $id)
    {
        $location = $request->param('location');
        $stockCar = StockModel::find($id);
        $stockCar->location = $location;
        $stockCar->deliver = 1;
        $stockCar->deliver_date = date('Y-m-d');
        $result = $stockCar->save();

        if($result){
            return "ok";
        }else{
            return 0;
        }

    }

}
